﻿using System;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Threading;
using System.Configuration;
using UFC.DBUtility;
using UFC.Common;
using System.Collections;
using System.Collections.Generic;

namespace TransferMesReportForm
{
    public class TransferMfgorderTouLiaoBoard
    {
        int Execute_rows = 400;//分批执行
        string strReportConn = ConfigurationManager.AppSettings["MesReportOracleConn"];
        public void RunMain()
        {
            LogHelp.SetConfig();
            LogHelp.WriteLog("TransferMfgorderTouLiaoBoard服务循环开始---------------------");

            System.Threading.Thread thAddData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    AddData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferMfgorderTouLiaoBoard服务-添加数据", e);
                }
            }));
            thAddData.Start();

            System.Threading.Thread thUpdateData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    UpdateData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferMfgorderTouLiaoBoard服务-更新数据", e);
                }
            }));
            thUpdateData.Start();

            //
            System.Threading.Thread thdeleteData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    DeleteData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferMfgorderTouLiaoBoard服务-删除数据", e);
                }
            }));
            thdeleteData.Start();

            //线程等待
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thAddData.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thUpdateData.ThreadState != System.Threading.ThreadState.Stopped);
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thdeleteData.ThreadState != System.Threading.ThreadState.Stopped);

            LogHelp.WriteLog("TransferMfgorderTouLiaoBoard服务循循环结束---------------------");
        }
        /// <summary>
        /// 查询需要结转的记录
        /// </summary>
        /// <returns></returns>
        private DataTable Get_MfgorderTouLiaoBoard()
        {
            string sql = @"select f.factoryname,cc.mfgordername,m.qty mfgqty,cc.productname,to_char(m.plannedstartdate,'yyyy/mm/dd') plannedstartdate,cc.containername,
                                 cc.qty,to_char(cc.materialdate,'yyyy/mm/dd hh24:mi:ss') materialdate,
                                 0 scrapqty,cc.isinstore,c.originalqty,cc.isselfmfg,u.attributevalue factorycode,cc.containerid
                          from containercurrentinfo cc
                          inner join mfgorder m on m.mfgorderid=cc.mfgorderid
                          left join orderstatus os on os.orderstatusid=m.orderstatusid  
                          left join container c on c.containerid=cc.containerid
                          left join factory f on f.factoryid=cc.originalfactoryid
                          left join userattribute u on u.parentid=f.factoryid and u.userattributename='分厂代码'       
                          where not exists
                          (SELECT 1
                                  FROM r_mfgordertouliaoboard@mesreport mf
                                 WHERE mf.containerid = cc.containerid)
                           and cc.workflowroute is null and nvl(os.orderstatusname,'打开')<>'关闭'
                           and u.attributevalue in ('600','601','607','608','609')
                           and rownum<10001                            
                           ";
            DataSet ds = OracleHelper.Query(sql);
            return ds.Tables[0];
        }
        /// <summary>
        /// 插入数据方法
        /// </summary>
        public void AddData()
        {
            DataTable dt = Get_MfgorderTouLiaoBoard();
            string insert_sql = @"insert into r_mfgordertouliaoboard(factoryname, mfgordername, mfgqty, productname, containername, 
                                              qty, scrapqty, isinstore, originalqty, isselfmfg, factorycode, plannedstartdate, materialdate,containerid) 
                                  values('{0}','{1}','{2}','{3}','{4}', '{5}','{6}','{7}','{8}','{9}','{10}',to_date('{11}','yyyy/mm/dd'),to_date('{12}','yyyy/mm/dd hh24:mi:ss'),'{13}')";

            string sqlLossqty = @" select sum(nvl(csw.lossqty,0)) lossqty from containerstepworkreport csw where csw.containerid='{0}' and csw.isuse=0";

            ArrayList sqlList = new ArrayList();
            string factoryname = ""; string mfgordername = ""; string mfgqty = ""; string productname = ""; string containername = "";
            string qty = ""; string scrapqty = ""; string isinstore = ""; string originalqty = ""; string isselfmfg = "";
            string factorycode = ""; string plannedstartdate = ""; string materialdate = ""; string containerid = "";

            DataTable dtLoss = new DataTable();
            foreach (DataRow dr in dt.Rows)
            {
                factoryname = dr["factoryname"].ToString();
                mfgordername = dr["mfgordername"].ToString();
                mfgqty = dr["mfgqty"].ToString();
                productname = dr["productname"].ToString();
                containername = dr["containername"].ToString();
                qty = dr["qty"].ToString();
                isinstore = dr["isinstore"].ToString();
                originalqty = dr["originalqty"].ToString();
                isselfmfg = dr["isselfmfg"].ToString();
                factorycode = dr["factorycode"].ToString();
                plannedstartdate = dr["plannedstartdate"].ToString();
                materialdate = dr["materialdate"].ToString();
                containerid = dr["containerid"].ToString();
                if (containername.Contains("'"))
                {
                    containername = containername.Replace("'", "''");
                }

                scrapqty = "";
                dtLoss = OracleHelper.Query(string.Format(sqlLossqty, containerid)).Tables[0];
                if (dtLoss.Rows[0]["lossqty"] != DBNull.Value)
                {
                    scrapqty = dtLoss.Rows[0]["lossqty"].ToString();
                }

                sqlList.Add(string.Format(insert_sql, factoryname, mfgordername, mfgqty, productname, containername,
                                              qty, scrapqty, isinstore, originalqty, isselfmfg, factorycode, plannedstartdate, materialdate, containerid));

                if (sqlList.Count == Execute_rows)
                {
                    OracleHelper.ExecuteSqlTran(sqlList, strReportConn);//此方法本身已经捕捉异常，再加无效
                    LogHelp.WriteLog("TransferMfgorderTouLiaoBoard服务本次" + sqlList.Count + "笔插入完成");
                    sqlList.Clear();
                }
            }
            if (sqlList.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqlList, strReportConn);
                sqlList.Clear();
                LogHelp.WriteLog("TransferMfgorderTouLiaoBoard服务本次" + sqlList.Count + "笔插入完成");
            }
        }
        /// <summary>
        /// 数据变更处理
        /// </summary>
        private void UpdateData()
        {
            string QuerySql = @"
                    select rm.id from r_mfgordertouliaoboard@mesreport rm
                    left join containercurrentinfo cc on cc.containerid=rm.containerid
                    left join mfgorder m on m.mfgordername=rm.mfgordername
                    left join (select containerid,nvl(sum(nvl(csw.lossqty,0)),0) lossqty  from containerstepworkreport csw group by csw.containerid) csw
                     on csw.containerid=rm.containerid
                    where rm.qty!=cc.qty or rm.isinstore<>cc.isinstore or  
                          nvl(rm.materialdate,to_date('2001/1/1 01:01:01','yyyy/mm/dd hh24:mi:ss'))<>nvl(cc.materialdate,to_date('2001/1/1 01:01:01','yyyy/mm/dd hh24:mi:ss'))
                    or csw.lossqty<>nvl(rm.scrapqty,0)
                    ";
            DataTable dtQuery = OracleHelper.Query(QuerySql).Tables[0];
            ArrayList sqlList = new ArrayList();
            foreach (DataRow dr in dtQuery.Rows)
            {
                sqlList.Add(string.Format("delete from r_mfgordertouliaoboard where id='{0}'", dr["id"].ToString()));
            }
            if (sqlList.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqlList, strReportConn);
                sqlList.Clear();
                LogHelp.WriteLog("TransferMfgorderTouLiaoBoard服务处理数据变更的记录完成");
            }
        }
        private void DeleteData()
        {
            string QuerySql =
                   @" select id from r_mfgordertouliaoboard@mesreport rf
                        where not exists(select 1 from containercurrentinfo cc                                    
                                     where cc.containerid=rf.containerid
                                     ) and rownum<501 ";
            DataTable dtDelete = OracleHelper.Query(QuerySql).Tables[0];
            ArrayList sqlList = new ArrayList();
            foreach (DataRow dr in dtDelete.Rows)
            {
                sqlList.Add(string.Format("delete from r_mfgordertouliaoboard where id='{0}'", dr["id"].ToString()));
            }
            if (sqlList.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(sqlList, strReportConn);
                LogHelp.WriteLog("TransferMfgorderTouLiaoBoard服务删除containercurrentinfo批次不存在的记录完成");
                sqlList.Clear();
            }
        }
    }
}
